package excel;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import alList.Grade;
import alList.Lesson;
import alList.Student;
public class FormImport {

//读学生成绩表，第一行的列标题为：课程编号，学生学号，学生成绩
    public static List<Grade> readGradeInfo(String file) throws FileNotFoundException, IOException, InvalidFormatException {
        InputStream ins = null;
        Workbook wb = null;
        ins = new FileInputStream(new File(file));
        wb = WorkbookFactory.create(ins);
        ins.close();

        Sheet sheet = wb.getSheetAt(0);

        List<Grade> gradeList = new ArrayList<Grade>();
        for (Row row : sheet) {
            if (row.getRowNum() < 1) continue;
            if (row.getLastCellNum() < 3) continue;
            row.getCell(0).setCellType(HSSFCell.CELL_TYPE_STRING);
            row.getCell(1).setCellType(HSSFCell.CELL_TYPE_STRING);
            row.getCell(2).setCellType(HSSFCell.CELL_TYPE_STRING);
            String claNum = row.getCell(0).getStringCellValue();
            String stuNum = row.getCell(1).getStringCellValue();
            String mark = row.getCell(2).getStringCellValue();
            //学生成绩可能是分数，也可能是等级
            Grade gradeInfo;
            try {
            	int score = Integer.parseInt(mark);
            	gradeInfo = new Grade(claNum, stuNum, score);
            }catch(NumberFormatException e) {
            	gradeInfo = new Grade(claNum, stuNum, mark);
            }

            gradeList.add(gradeInfo);
        }
        
        return gradeList;
    }
 
//读学生基本信息，从左至右为学号num, 姓名name, 班级cla, 专业spec, 系别depart
    public static List<Student> readStuInfo(String file) throws FileNotFoundException, IOException, InvalidFormatException {
        InputStream ins = null;
        Workbook wb = null;
        ins = new FileInputStream(new File(file));
        wb = WorkbookFactory.create(ins);
        ins.close();

        Sheet sheet = wb.getSheetAt(0);

        List<Student> stuList = new ArrayList<Student>();
        for (Row row : sheet) {
            if (row.getRowNum() < 1) continue;
            if (row.getCell(0) == null || row.getCell(1) == null) continue;
            
            row.getCell(0).setCellType(HSSFCell.CELL_TYPE_STRING);
            String num = row.getCell(0).getStringCellValue();
            
            String name = row.getCell(1).getStringCellValue();
            
            row.getCell(2).setCellType(HSSFCell.CELL_TYPE_STRING);
            String cla = row.getCell(2).getStringCellValue();  
    		
            String spec = row.getCell(3).getStringCellValue();
            
            String depart = row.getCell(4).getStringCellValue();

            Student student = new Student(num, name, cla, spec, depart);
            stuList.add(student);
        }
        return  stuList;
    }
    
//读课程基本信息，从左至右为课程编号num, 课程名name, 学分credit, 任课老师teach
    public static List<Lesson> readClaInfo(String file) throws FileNotFoundException, IOException, InvalidFormatException {
        InputStream ins = null;
        Workbook wb = null;
        ins = new FileInputStream(new File(file));
        wb = WorkbookFactory.create(ins);
        ins.close();
        
        Sheet sheet = wb.getSheetAt(0);
        
        List<Lesson> claList = new ArrayList<Lesson>();
        
        for (Row row : sheet) {
            if (row.getRowNum() < 1) continue;
            if (row.getCell(0) == null || row.getCell(1) == null) continue;
            
            row.getCell(0).setCellType(HSSFCell.CELL_TYPE_STRING);
            String num = row.getCell(0).getStringCellValue();
            
            String name = row.getCell(1).getStringCellValue();
            
            double credit = row.getCell(2).getNumericCellValue();
    		
            String teach = row.getCell(3).getStringCellValue();
            String nature = row.getCell(4).getStringCellValue();
            String exam = row.getCell(5).getStringCellValue();
            
            Lesson lesson = new Lesson(num, name, credit, teach, nature, exam);
            claList.add(lesson);
        }
        return  claList;
    }


}
